package com.atlin.project.service.impl;

import com.atlin.project.bizmq.bi.BiMessageProducer;
import com.atlin.project.common.ErrorCode;
import com.atlin.project.contant.CommonConstant;
import com.atlin.project.exception.BusinessException;
import com.atlin.project.exception.ThrowUtils;
import com.atlin.project.mapper.ChartMapper;
import com.atlin.project.model.domain.chart.Chart;
import com.atlin.project.model.domain.chart.ChartOriginalData;
import com.atlin.project.model.dto.chart.ChartQueryRequest;
import com.atlin.project.model.dto.chart.ChartUpdateRequest;
import com.atlin.project.model.enums.chart.ChartStatusEnum;
import com.atlin.project.model.vo.chart.ChartVO;
import com.atlin.project.service.ChartService;
import com.atlin.project.utils.sql.SqlUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.redisson.api.RLock;
import org.redisson.api.RedissonClient;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * @author atlin
 * @description 针对表【chart(图表信息表)】的数据库操作Service实现
 * @createDate 2024-11-12 13:17:21
 */
@Service
@Slf4j
public class ChartServiceImpl extends ServiceImpl<ChartMapper, Chart>
        implements ChartService {

    @Resource
    private ChartMapper chartMapper;

    private static String databaseName;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private RedissonClient redissonClient;

    @Resource
    private BiMessageProducer biMessageProducer;

    @Override
    public void valid(Chart chart, boolean add) {

    }

    @Override
    public ChartVO getChartVO(Chart chart, HttpServletRequest request) {
        ChartVO chartVO = ChartVO.objToVo(chart);
        // todo other operation
        return chartVO;
    }

    /**
     * 获取查询包装类（用户根据哪些字段查询，根据前端传来的请求对象，得到 mybatis 框架支持的查询 QueryWrapper 类）
     *
     * @param chartQueryRequest
     * @return
     */
    @Override
    public QueryWrapper<Chart> getQueryWrapper(ChartQueryRequest chartQueryRequest) {
        QueryWrapper<Chart> queryWrapper = new QueryWrapper<>();
        if (chartQueryRequest == null) {
            return queryWrapper;
        }
        Long id = chartQueryRequest.getId();
        String name = chartQueryRequest.getName();
        String sortField = chartQueryRequest.getSortField();
        String sortOrder = chartQueryRequest.getSortOrder();
        String chartType = chartQueryRequest.getChartType();
        Integer status = chartQueryRequest.getStatus();
        Long userId = chartQueryRequest.getUserId();

        queryWrapper.eq(ObjectUtils.isNotEmpty(id), "id", id);
        queryWrapper.eq(ObjectUtils.isNotEmpty(status), "status", status);
        queryWrapper.eq(StringUtils.isNotBlank(name), "name", name);
        queryWrapper.eq(StringUtils.isNotBlank(chartType), "chartType", chartType);

        queryWrapper.eq(ObjectUtils.isNotEmpty(userId), "userId", userId);
        queryWrapper.eq("isDelete", false);
        queryWrapper.orderBy(SqlUtils.validSortField(sortField),
                sortOrder.equals(CommonConstant.SORT_ORDER_ASC), sortField);

        return queryWrapper;
    }

    @Override
    public Page<ChartVO> getChartVOPage(Page<Chart> chartPage, HttpServletRequest request) {
        List<Chart> chartList = chartPage.getRecords();
        Page<ChartVO> chartVOPage = new Page<>(chartPage.getCurrent(), chartPage.getSize(), chartPage.getTotal());
        if (CollectionUtils.isEmpty(chartList)) {
            return chartVOPage;
        }

        // 填充信息
        List<ChartVO> chartVOList = chartList.stream().map(ChartVO::objToVo).collect(Collectors.toList());
        chartVOPage.setRecords(chartVOList);
        return chartVOPage;
    }

    @PostConstruct
    private void init() throws SQLException {
        databaseName = jdbcTemplate.queryForObject(
                "SELECT DATABASE()",
                String.class
        );
    }

    private String getDatabaseName() {
        return databaseName;
    }

    @Override
    public boolean chartOptionValidator(String option) {
        // todo option 校验
        return true;
    }


    /**
     * 检查表是否存在
     */
    private boolean checkTableExists(String tableName) {
        String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + getDatabaseName() + "' AND TABLE_NAME = '" + tableName + "'";
        try {
            String result = jdbcTemplate.queryForObject(sql, String.class);
            return result != null;
        } catch (EmptyResultDataAccessException e) {
            // 查询结果为空
            return false;
        }
    }


    /**
     * 保存表以及数据
     */
    @Override
    public String saveChartData(long chartId, String chartData) {
        String tableName = String.format("chart_%s", chartId);

        ChartOriginalData chartOriginalData = ChartOriginalData.csvToObj(chartData);
        if (chartOriginalData == null) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "原始数据为空");
        }
        // 表头
        List<String> headers = chartOriginalData.getHeaders();
        List<Map<String, Object>> rows = chartOriginalData.getRows();

        // 检查表是否存在
        if (!checkTableExists(tableName)) {
            // 构建创建表的SQL语句
            String createSql = String.format("CREATE TABLE %s (idx BIGINT(20) NOT NULL, %s ,UNIQUE(idx))",
                    tableName,
                    String.join(", ", headers.stream().skip(1).map(header -> header + " VARCHAR(128) NULL").collect(Collectors.toList())));

            System.out.println("createSql：\n" + createSql);
            try {
                chartMapper.createChartDataTable(createSql);
            } catch (Exception e) {
                log.error("createChartDataTable failure", e);
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "原始数据存在问题");
            }
        }
        try {
            int rowsAffected = insertChartData(tableName, chartOriginalData);
            ThrowUtils.throwIf(rowsAffected == 0, ErrorCode.PARAMS_ERROR, "原始数据为空");
        } catch (Exception e) {
            log.error("insertChartData failure", e);
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "原始数据存在问题");
        }

        return tableName;
    }


    /**
     * 插入数据
     */
    public int insertChartData(String tableName, ChartOriginalData chartOriginalData) {
        List<String> headers = chartOriginalData.getHeaders();
        List<Map<String, Object>> rows = chartOriginalData.getRows();

        // 构建插入数据的SQL语句
        StringBuilder insertSqlSb = new StringBuilder(String.format("INSERT INTO %s VALUES ", tableName));
        for (int i = 0; i < rows.size(); i++) {
            Map<String, Object> row = rows.get(i);
            List<String> values = headers.stream().map(header -> "'" + row.get(header) + "'").collect(Collectors.toList());
            insertSqlSb.append("(").append(String.join(", ", values)).append(")");
            if (i < rows.size() - 1) {
                insertSqlSb.append(",");
            }
        }
        insertSqlSb.append(";");

        System.out.println("insertSqlSb：" + insertSqlSb.toString());
        return chartMapper.insertChartData(insertSqlSb.toString());
    }


    @Override
    public void handleChartUpdateError(long chartId, String execMessage) {
        handleChartUpdateError(chartId, execMessage, ChartStatusEnum.FAILURE);
    }

    @Override
    public void handleChartUpdateError(long chartId, String execMessage, ChartStatusEnum chartStatusEnum) {
        Chart updateChartResult = new Chart();
        updateChartResult.setId(chartId);
        updateChartResult.setStatus(chartStatusEnum.getValue());
        updateChartResult.setExecMessage(execMessage);
        boolean updateResult = this.updateById(updateChartResult);
        if (!updateResult) {
            log.error("更新图表失败状态失败 " + chartId + "," + execMessage);
        }
    }

    /**
     * 获取数据 -- csv
     */
    @Override
    public String getChartOriginalCsvData(long chartId) {
        return ChartOriginalData.objToCsv(this.getChartOriginalObjData(chartId));
    }

    @Override
    public ChartOriginalData getChartOriginalObjData(long chartId) {
        if (chartId <= 0) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "图表ID不存在");
        }
        String sql = String.format("select * from chart_%s", chartId);
        try {
            List<Map<String, Object>> chartData = chartMapper.queryChartData(sql);
            return ChartOriginalData.build(chartData);
        } catch (Exception e) {
            log.error("queryChartData execute failure chartId={}", chartId, e);
        }
        return new ChartOriginalData();
    }

    @Override
    @Transactional
    public boolean updateChart(ChartUpdateRequest chartUpdateRequest) {
        Long id = chartUpdateRequest.getId();
        String name = chartUpdateRequest.getName();
        String genChart = chartUpdateRequest.getGenChart();
        ChartOriginalData chartOriginalData = chartUpdateRequest.getChartOriginalData();

        // 更新 chart 表
        UpdateWrapper<Chart> updateWrapper = new UpdateWrapper<>();
        updateWrapper.lambda().eq(Chart::getId, id)
                .set(StringUtils.isNotBlank(name), Chart::getName, name)
                .set(StringUtils.isNotBlank(genChart), Chart::getGenChart, genChart);
        //更新 chart表
        boolean update = this.update(updateWrapper);

        // 更新chart_数据表
        if (chartOriginalData == null || chartOriginalData.getRows().isEmpty()) return update;

        List<String> headers = chartOriginalData.getHeaders();
        List<Map<String, Object>> rows = chartOriginalData.getRows();

        String tableName = String.format("chart_%s", id);
        int executeCount = 0;
        for (Map<String, Object> currentRow : rows) {
            // 构建 INSERT 部分
            String columns = String.join(",", headers);
            String values = headers.stream()
                    // idx 若不存在 + '' 
                    .map(header -> {
                        Object value = currentRow.get(header);
                        if (value == null) {
                            String maxIdxQuery = String.format("SELECT IFNULL(MAX(idx), 0) FROM %s;", tableName);
                            long maxIdx = jdbcTemplate.queryForObject(maxIdxQuery, Long.class);
                            value = maxIdx + 1;
                        } else {
                            value = "'" + value + "'";
                        }
                        return value.toString();
                    })
                    .collect(Collectors.joining(","));

            String insertSql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, values);

            // 构建 UPDATE 部分
            String updateClause = headers.stream()
                    .map(key -> key + " = VALUES(" + key + ")")
                    .collect(Collectors.joining(","));

            String sql = String.format("%s ON DUPLICATE KEY UPDATE %s;", insertSql, updateClause);

            // 执行 SQL
            // INSERT INTO chart_1859242646573854721 (用户数,日期,idx) VALUES ('27','12号','12') ON DUPLICATE KEY UPDATE 用户数 = VALUES(用户数),日期 = VALUES(日期),idx = VALUES(idx);
            try {
                int result = chartMapper.executeSql(sql);
                executeCount += result;
            } catch (Exception e) {
                throw new BusinessException(ErrorCode.SYSTEM_ERROR, "修改原始数据表数据失败");
            }
        }

        return executeCount > 0;
    }

    @Override
    public void autoRetry() {
        RLock lock = redissonClient.getLock("bbeacons:chart:auto-retry:lock");
        try {
            if (lock.tryLock(0, -1, TimeUnit.MILLISECONDS)) {
                LocalDateTime startTime = LocalDateTime.now().minusDays(1).withHour(0).withMinute(0).withSecond(0);
                LocalDateTime endTime = LocalDateTime.now().minusDays(1).withHour(23).withMinute(59).withSecond(59);

                QueryWrapper<Chart> wrapper = new QueryWrapper<>();
                wrapper.eq("status", ChartStatusEnum.FAILURE.getValue());
                wrapper.ge("createTime", startTime);
                wrapper.le("createTime", endTime);
                List<Chart> list = list(wrapper);
                for (Chart chart : list) {
                    // 以失败状态重试 -- 不消耗用户的tokens
                    biMessageProducer.sendMessage(String.valueOf(chart.getId()));
                }
            }
        } catch (InterruptedException e) {
            log.error("autoRetry error", e);
        } finally {
            if (lock.isHeldByCurrentThread()) {
                log.info("unlock {}", Thread.currentThread().getId());
                lock.unlock();
            }
        }

    }


    @Deprecated
    public String getChartAllCsvData_(long chartId) {
        if (chartId <= 0) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "图表ID不存在");
        }
        String sql = String.format("select * from chart_%s", chartId);
        List<Map<String, Object>> chartData = null;
        try {
            chartData = chartMapper.queryChartData(sql);

        } catch (Exception e) {
            log.error("queryChartData execute failure chartId={}", chartId, e);
            throw new RuntimeException(e);
        }

        if (chartData != null && chartData.size() > 1) {
            // 首行表头 
            Map<String, Object> map = chartData.get(0);
            Set<String> keys = map.keySet();


            // 使用String.join连接键
            String header = String.join(",", keys) + "\n";

            // 使用StringBuilder构建数据行
            StringBuilder builder = new StringBuilder(header);
            for (Map<String, Object> chartDatum : chartData) {
                String row = keys.stream()
                        .map(key -> String.valueOf(chartDatum.get(key)))
                        .collect(Collectors.joining(",")) + "\n";
                builder.append(row);
            }

            return builder.toString();
        } else {
            return "";
        }
    }

    @Deprecated
    public String saveChartData_(long chartId, String chartData) {
        String tableName = String.format("chart_%s", chartId);
        String[] chartDataArr = chartData.split("\\n");
        if (chartDataArr.length < 2) {
            throw new RuntimeException("数据不存在");
        }
        // 检查表是否存在
        if (!checkTableExists(tableName)) {
            StringBuilder sb = new StringBuilder();
            sb.append("create table ").append(tableName).append("(");
            String[] fields = chartDataArr[0].split(",");
            for (int i = 0; i < fields.length; i++) {
                sb.append(fields[i]).append(" ").append("varchar(128) null");
                if (i < fields.length - 1) {
                    sb.append(",\n");
                }
            }
            sb.append(");");
            System.out.println("createSql：\n" + sb.toString());
            chartMapper.createChartDataTable(sb.toString());
        }
        try {
            int rowsAffected = insertChartData(tableName, chartDataArr);
        } catch (Exception e) {
            log.error("insertChartData failure", e);
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "原始数据存在问题");
        }
        return tableName;
    }

    /**
     * @param tableName
     * @param chartDataArr 首列-表头
     * @return
     */
    @Transactional
    @Deprecated
    public int insertChartData(String tableName, String[] chartDataArr) {
        if (chartDataArr == null || chartDataArr.length < 2) return 0;

        StringBuilder saveSqlSb = new StringBuilder();
        //insert into chart_1857041705290399746 values('1号','10'),('2号','20'),('3号','30'),('4号','90');
        saveSqlSb.append("insert into ").append(tableName).append(" values ");
        for (int i = 1; i < chartDataArr.length; i++) {
            String[] fields = chartDataArr[i].split(",");
            saveSqlSb.append("(");
            for (int j = 0; j < fields.length; j++) {
                saveSqlSb.append("'").append(fields[j]).append("'");
                if (j < fields.length - 1) {
                    saveSqlSb.append(",");
                }
            }
            if (i < chartDataArr.length - 1) {
                saveSqlSb.append("),");
            } else {
                saveSqlSb.append(");");
            }
        }
        return chartMapper.insertChartData(saveSqlSb.toString());
    }
}




